import pandas as pd
import numpy as np

df = pd.read_csv('F:/25MCM_C/C_data/merged1.csv', encoding='latin1', low_memory=False)


print("Columns:", df.columns)

df.columns = df.columns.str.strip().str.upper()
df['NOC']  = df['NOC'].astype(str).str.strip().str.upper()
df['HOST'] = df['HOST'].astype(str).str.strip().str.upper()  # host 也做同样处理

df['IS_HOST'] = (df['NOC'] == df['HOST'])
#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
df['GOLD']   = pd.to_numeric(df['GOLD'],   errors='coerce').fillna(0)
df['SILVER'] = pd.to_numeric(df['SILVER'], errors='coerce').fillna(0)
df['BRONZE'] = pd.to_numeric(df['BRONZE'], errors='coerce').fillna(0)
df['TOTAL']  = pd.to_numeric(df['TOTAL'],  errors='coerce').fillna(0)

group_sport = df.groupby(['NOC', 'SPORT'], as_index=False).agg({
    'GOLD':   'sum',
    'SILVER': 'sum',
    'BRONZE': 'sum',
    'TOTAL':  'sum'
})


group_sport['MEDALS'] = group_sport['GOLD'] + group_sport['SILVER'] + group_sport['BRONZE']
#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216

df['MEDALS'] = df['GOLD'] + df['SILVER'] + df['BRONZE']

host_medals    = df[df['IS_HOST'] == True]['MEDALS'].mean()
non_host_medals= df[df['IS_HOST'] == False]['MEDALS'].mean()

print(f"Average medals when country is host: {host_medals:.2f}")
print(f"Average medals when country is not host: {non_host_medals:.2f}")


host_sport = df.groupby(['IS_HOST','SPORT'], as_index=False)['MEDALS'].sum()

host_sport_mean = df.groupby(['IS_HOST','SPORT'], as_index=False)['MEDALS'].mean()
#比赛结束前最后一天售后群发布无水印可视化结果+无标注代码【可直接提交】为了防止倒卖， 论文写作过程中遗留数个致命问题，无关代码，该问题解决方式仅在官网授权售后群答疑，盗卖方式购买资料不提供答疑，感谢理解 美赛资料助攻购买链接+说明https://docs.qq.com/doc/p/f3dc6bffbf4dab58dbdfd3e5e5de18a2ad974216
important_sport_df = group_sport.sort_values(['NOC','MEDALS'], ascending=[True,False])

group_sport.to_csv('F:/25MCM_C/C_data/sport_medals_by_country.csv', index=False)
host_sport.to_csv('F:/25MCM_C/C_data/sport_medals_host_vs_nonhost.csv', index=False)
important_sport_df.to_csv('F:/25MCM_C/C_data/important_sport_by_country.csv', index=False)

print("Analysis complete. CSV outputs have been generated.")
